﻿/****************************** Module Header ******************************\
 * Module Name:  ExMethodForDataTable.cs
 * Module Version:  1.0.0 (2012-4-27)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to extension collection instance's method.  It supplies 
 * useful extension methods, such as ToString(), FetchString() and so on.
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Drawing;
using System.Text;
using System.IO;

namespace UCF.Core
{
    using Data;
    /// <summary>
    /// 数据表对象扩展类
    /// </summary>
    public static class ExMethodForDataTable
    {
        /// <summary>
        /// 统计指定列的货币总和
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="colIndex">列索引位置</param>
        /// <returns></returns>
        public static int SumInteger(this DataTable dt, int colIndex)
        {
            if (dt == null || dt.Rows.Count == 0) return 0;
            int iOut = 0;
            foreach (DataRow r in dt.Rows)
            {
                iOut += r[colIndex].ToString().ToInt();
            }
            return iOut;
        }

        /// <summary>
        /// 统计指定列的数值总和
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="colIndex">列索引位置</param>
        /// <returns></returns>
        public static double SumDouble(this DataTable dt, int colIndex)
        {
            if (dt == null || dt.Rows.Count == 0) return 0.00;
            double dblOut = 0;
            foreach (DataRow r in dt.Rows)
            {
                dblOut += r[colIndex].ToString().ToDouble();
            }
            return dblOut;
        }

        /// <summary>
        /// 检测指定列是否存在与指定的值相等的记录
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="v">要比较的值</param>
        /// <param name="colIndex">要比较的列索引编号</param>
        /// <returns></returns>
        public static bool Exists(this DataTable dt, object v, int colIndex = 0)
        {
            if (dt == null || dt.Rows.Count == 0) return false;
            foreach (DataRow r in dt.Rows)
            {
                if (r[colIndex].ToString() == v.ToString()) return true;
            }
            return false;
        }

        /// <summary>
        /// 移除指定列中与指定值相等的数据行
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="v">要比较的值</param>
        /// <param name="colIndex">值对应的列索引编号</param>
        public static void RemoveByValue(this DataTable dt, object v, int colIndex = 0)
        {
            if (dt == null || dt.Rows.Count == 0) return;
            int i = -1;
            foreach (DataRow r in dt.Rows)
            {
                if (r[colIndex].ToString() == v.ToString()) i = dt.Rows.IndexOf(r);
            }
            if (i > -1) dt.Rows.RemoveAt(i);
        }

        /// <summary>
        /// 移除指定数据行索引编号所在的数据行
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="rowIndex"></param>
        public static void RemoveByRowIndex(this DataTable dt, int rowIndex)
        {
            dt.Rows.RemoveAt(rowIndex);
        }

        /// <summary>
        /// 添加一条数据行,并检测是否主键值重复
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="pkColIndex">主键值所在的数据列的索引编号</param>
        /// <param name="vals"></param>
        public static void AddRow(this DataTable dt, int pkColIndex = 0, params object[] vals)
        {
            if (vals == null) return;
            if (dt == null) dt = new DataTable();
            DataRow dr;
            if (dt.Columns.Count == 0)
            {
                for (int i = 0; i < vals.Length; i++)
                {
                    dt.Columns.Add(i.ToString());
                }
                dr = dt.NewRow();
                dr.ItemArray = vals;
                dt.Rows.InsertAt(dr, 0);
                return;
            }
            foreach (DataRow r in dt.Rows)
            {
                if (pkColIndex >= 0 && (r[pkColIndex].ToString() == vals[pkColIndex].ToString())) return;
            }
            dr = dt.NewRow();
            dr.ItemArray = vals;
            dt.Rows.InsertAt(dr, 0);
        }

        /// <summary>
        /// 设置表格列名称
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="names"></param>
        public static void SetColumnsName(this DataTable dt, params string[] names)
        {
            if (dt.Columns.Count == 0)
            {
                foreach (string s in names)
                {
                    dt.Columns.Add(s);
                }
                return;
            }
            int i = 0;
            foreach (string s in names)
            {
                dt.Columns[i].ColumnName = s;
                i++;
            }
        }

        /// <summary>
        /// 追加表格列名称
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="names"></param>
        public static void AppendColumnsName(this DataTable dt, params string[] names)
        {
            foreach (string s in names)
            {
                if (!dt.Columns.Contains(s)) dt.Columns.Add(s);
            }
        }

        /// <summary>
        /// 更新指定的数据行索引编号对应的数据行数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="rowId">数据行索引编号</param>
        /// <param name="colsIndex">要更新的数据列索引编号集合</param>
        /// <param name="vals">要更新的数据值集合</param>
        public static void UpdateByRowIndex(this DataTable dt, int rowId, int[] colsIndex, params object[] vals)
        {
            if (dt == null || dt.Rows.Count == 0) return;
            for (int i = 0; i < colsIndex.Length; i++)
            {
                dt.Rows[rowId][colsIndex[i]] = vals[i];
            }
        }


        public static DataTable Update(this DataTable dt, string filter, string[] fields, params string[] values)
        {
            DataRow[] rs = dt.Select(filter);
            if (rs.Length == 0) return dt;
            int i;
            foreach (DataRow r in rs)
            {
                i = 0;
                foreach (string s in fields)
                {
                    r[s] = values[i];
                    i++;
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取指定列名的所有值的列表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static IList<V> GetListOfColumnValue<V>(this DataTable dt, string columnName)
        {
            IList<V> list = new List<V>();
            foreach (DataRow r in dt.Rows)
            {
                if (dt.Columns.Contains(columnName))
                {
                    if (r[columnName] == DBNull.Value) continue;
                    list.Add((V)r[columnName]);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取指定列名的所有值的列表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static IList<V> GetListOfColumnValue<V>(this DataTable dt, int index)
        {
            IList<V> list = new List<V>();
            foreach (DataRow r in dt.Rows)
            {
                if (r[index] != DBNull.Value) list.Add((V)r[index]);
            }
            return list;
        }

        /// <summary>
        /// 获取数据表中的字段名称列表
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static IList<string> GetListOfFieldName(this DataTable dt)
        {
            IList<string> list = new List<string>();
            foreach (DataColumn c in dt.Columns)
            {
                list.Add(c.ColumnName);
            }
            return list;
        }

        /// <summary>
        /// 数据表转换成字典
        /// </summary>
        /// <typeparam name="K"></typeparam>
        /// <typeparam name="V"></typeparam>
        /// <param name="dt"></param>
        /// <param name="colKey"></param>
        /// <param name="colVal"></param>
        /// <returns></returns>
        public static Dictionary<K, V> ToDictionary<K, V>(this DataTable dt, int colKey = 0, int colVal = 1)
        {
            Dictionary<K, V> dict = new Dictionary<K, V>();
            foreach (DataRow r in dt.Rows)
            {
                dict.Add((K)r[0], (V)r[1]);
            }
            return dict;
        }

        /// <summary>
        /// 添加行并返回新数据表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static DataTable AppendRowR(this DataTable dt, params object[] args)
        {
            dt.Rows.Add(args);
            return dt;
        }

        /// <summary>
        /// 添加行并返回新数据表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static DataTable PrependRowR(this DataTable dt, params object[] args)
        {
            DataRow dr = dt.NewRow();
            dr.ItemArray = args;
            dt.Rows.InsertAt(dr, 0);
            return dt;
        }

        /// <summary>
        /// 设置表格名称
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public static DataTable SetTableName(this DataTable dt, string name)
        {
            dt.TableName = name;
            return dt;
        }

        /// <summary>
        /// 复制表结构
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static DataTable CopySehcma(this DataTable dt)
        {
            DataTable dtn = new DataTable();
            foreach (DataColumn dc in dt.Columns)
            {
                dtn.Columns.Add(dc.ColumnName, dc.DataType);
            }
            if (dt.TableName.Length > 0) dtn.TableName = dt.TableName;
            return dtn;
        }

        public static string ToText(this DataTable dt, string colSplitor = " | ", string rowSplitor = "\n")
        {
            StringBuilder sb = new StringBuilder();
            int i = dt.Columns.Count;
            int n = 0;
            foreach (DataColumn c in dt.Columns)
            {
                i--;
                sb.AppendFormat("[{2}]{0}{1}", c.ColumnName, i > 0 ? colSplitor : string.Empty, n);
                n++;
            }
            if (sb.Length > 0) sb.Append(rowSplitor);
            foreach (DataRow r in dt.Rows)
            {
                n = 0;
                i = dt.Columns.Count;
                foreach (object v in r.ItemArray)
                {
                    i--;
                    sb.AppendFormat("[{2}]{0}{1}", v, i > 0 ? colSplitor : string.Empty, n);
                    n++;
                }
                sb.Append(rowSplitor);
            }
            return sb.ToString();
        }


        public static string ToKVText(this DataTable dt, int kColId = 1, int vColId = 0, string colSplitor = "=", string rowSplitor = "&")
        {
            if (dt == null) return string.Empty;
            if (dt.Columns.Count < kColId || dt.Columns.Count < vColId) return string.Empty;
            StringBuilder sb = new StringBuilder();
            foreach (DataRow r in dt.Rows)
            {
                sb.AppendFormat("{0}{1}{2}{3}", r[vColId], colSplitor, r[kColId], rowSplitor);
            }
            return sb.ToString();
        }

        /// <summary>
        /// 联合多个表
        /// <para>2012-10-30</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dts"></param>
        /// <returns></returns>
        public static DataTable Join(this DataTable dt, params DataTable[] dts)
        {
            foreach (DataTable o in dts)
            {
                foreach (DataRow dr in o.Rows)
                {
                    dt.Rows.Add(dr.ItemArray);
                }
            }
            return dt;
        }

        /// <summary>
        /// 保存到csv
        /// <para>2012-11-20</para>
        /// </summary>
        /// <param name="dgv"></param>
        public static bool SaveAsExcel(this DataTable dt)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Title = "保存到Excel文件";
            sfd.Filter = "Excel文件(*.xls) |*.xls |所有文件(*.*) |*.*";
            sfd.FilterIndex = 1;
            if (sfd.ShowDialog() != DialogResult.OK) return false;
            string FileName = sfd.FileName;
            if (File.Exists(FileName)) File.Delete(FileName);
            FileStream objFileStream;
            StreamWriter objStreamWriter;
            string strLine = string.Empty, rowstr;
            objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
            objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                strLine = strLine + dt.Columns[i].ColumnName.ToString() + Convert.ToChar(9);
            }
            objStreamWriter.WriteLine(strLine);
            strLine = string.Empty;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][0] == null)
                    strLine = strLine + " " + Convert.ToChar(9);
                else
                    strLine = strLine + dt.Rows[i][0].ToString() + Convert.ToChar(9);
                for (int j = 1; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j] == null)
                        strLine = strLine + " " + Convert.ToChar(9);
                    else
                    {
                        rowstr = string.Format("'{0}", dt.Rows[i][j].ToString());
                        if (rowstr.IndexOf("\r\n") > 0)
                            rowstr = rowstr.Replace("\r\n", " ");
                        if (rowstr.IndexOf("\t") > 0)
                            rowstr = rowstr.Replace("\t", " ");
                        strLine = strLine + rowstr + Convert.ToChar(9);
                    }

                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";
            }
            objStreamWriter.Close();
            objFileStream.Close();
            return true;
        }

        
        public static bool SaveAsExcelSengFeng(this DataTable dt)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Title = "保存到Excel文件";
            sfd.Filter = "Excel文件(*.xls) |*.xls |所有文件(*.*) |*.*";
            sfd.FilterIndex = 1;
            if (sfd.ShowDialog() != DialogResult.OK) return false;
            string FileName = sfd.FileName;
            if (File.Exists(FileName)) File.Delete(FileName);
            FileStream objFileStream;
            StreamWriter objStreamWriter;
            string strLine = string.Empty;
            objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
            objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.GetEncoding("GB2312"));

            //空俩行
            strLine = strLine + "\r\n" + Convert.ToChar(9);
            strLine = strLine + "\r\n" + Convert.ToChar(9);

            //列
            //表头首先要空三列
            strLine = strLine + Convert.ToChar(9);
            //strLine = strLine + Convert.ToChar(9);

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                //strLine = strLine + dt.Columns[i].ColumnName.ToString() + Convert.ToChar(9);
            }
            strLine = strLine + "收件公司" + Convert.ToChar(9) + "联系人" + Convert.ToChar(9) + "联系电话" + Convert.ToChar(9) + "手机号码" + Convert.ToChar(9);
            strLine = strLine + "收件详细地址" + Convert.ToChar(9) + "付款方式" + Convert.ToChar(9) + "托寄物内容" + Convert.ToChar(9) + "托寄物数量" + Convert.ToChar(9);
            strLine = strLine + "件数" + Convert.ToChar(9) + "寄方客户备注" + Convert.ToChar(9) + "是否代收货款" + Convert.ToChar(9) + "代收货款金额" + Convert.ToChar(9);

            objStreamWriter.WriteLine(strLine);
            strLine = string.Empty;

            string rowStrNew = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][0] == null)
                {
                    strLine = strLine + " " + Convert.ToChar(9);
                }
                else
                {
                    //行中首先要空三列
                    strLine = strLine + Convert.ToChar(9);
                    strLine = strLine + Convert.ToChar(9);
                    strLine = strLine + "亲" + Convert.ToChar(9);

                    strLine = strLine + dt.Rows[i][0].ToString() + Convert.ToChar(9);
                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        if (dt.Rows[i][j] == null)
                            strLine = strLine + " " + Convert.ToChar(9);
                        else
                        {
                            string rowstr = "";
                            rowstr = dt.Rows[i][j].ToString();
                            //手机号码前空一列
                            if (j == 2)
                            {
                                rowstr = "\t" + rowstr;
                            }


                            if (rowstr.IndexOf("\r\n") > 0)
                                rowstr = rowstr.Replace("\r\n", " ");
                            if (rowstr.IndexOf("\t") > 0)
                                rowstr = rowstr.Replace("\t", " ");

                            if (j == 3)
                            {
                                rowStrNew = "1" + Convert.ToChar(9);
                                rowStrNew += "茶叶" + Convert.ToChar(9);
                                rowStrNew += "1" + Convert.ToChar(9);
                                rowStrNew += "1" + Convert.ToChar(9);
                                rowStrNew += "" + Convert.ToChar(9);
                                rowStrNew += "Y" + Convert.ToChar(9);

                                rowstr = rowStrNew + rowstr + Convert.ToChar(9);

                                rowstr = rowstr + Convert.ToChar(9);
                                rowstr = rowstr + Convert.ToChar(9);
                                rowstr = rowstr + Convert.ToChar(9);
                                rowstr = rowstr + Convert.ToChar(9);
                                rowstr = rowstr + Convert.ToChar(9);
                                rowstr = rowstr + Convert.ToChar(9);
                                rowstr = rowstr + Convert.ToChar(9);
                                rowstr = rowstr + " " + Convert.ToChar(9);
                            }

                            strLine = strLine + rowstr + Convert.ToChar(9);



                        }

                    }
                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";
            }
            objStreamWriter.Close();
            objFileStream.Close();
            return true;
        }

        /// <summary>
        /// 保存成excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public static string SaveToExcel(this DataTable dt, string sheetName = "Sheet1", string filter = "")
        {
            if (dt == null) return "没有记录!";
            try
            {
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Title = "保存到Excel文件";
                sfd.Filter = "Excel文件(*.xls) |*.xls |所有文件(*.*) |*.*";
                sfd.FilterIndex = 1;
                if (sfd.ShowDialog() != DialogResult.OK) return "保存已取消!";
                string FileName = sfd.FileName;
                ExcelHelper eh = new ExcelHelper(FileName);
                eh.ExecuteNonQuery(eh.GetCreateTableSql(dt, sheetName));
                OleDbConnection conn = eh.GetConnection();

                OleDbDataAdapter da = new OleDbDataAdapter("Select * From [" + sheetName + "$]", conn);
                DataTable dtExcel = new DataTable();
                da.Fill(dtExcel);
                da.InsertCommand = eh.GetInsertCommand(sheetName, dt, conn);
                DataRow dr_excel;
                string ColumnName;
                foreach (DataRow dr in dt.Select(filter))
                {
                    dr_excel = dtExcel.NewRow();
                    foreach (DataColumn c in dt.Columns)
                    {
                        ColumnName = c.ColumnName;
                        dr_excel[ColumnName] = dr[ColumnName];
                    }
                    dtExcel.Rows.Add(dr_excel);
                }
                da.Update(dtExcel);
                conn.Close();
                return "SUCCESS";

            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                return ex.Message;
            }
        }

        /// <summary>
        /// 保存到excel，并导入到顺丰大客户
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public static string SaveToExcelForSF(this DataTable dt, string sheetName = "Sheet1", string filter = "")
        {
            if (dt == null) return "没有记录!";
            try
            {
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Title = "保存到Excel文件";
                sfd.Filter = "Excel文件(*.xls) |*.xls |所有文件(*.*) |*.*";
                sfd.FilterIndex = 1;
                if (sfd.ShowDialog() != DialogResult.OK) return "保存已取消!";
                string FileName = sfd.FileName;
                ExcelHelper eh = new ExcelHelper(FileName);
                eh.ExecuteNonQuery(eh.GetCreateTableSql(dt, sheetName));
                OleDbConnection conn = eh.GetConnection();

                OleDbDataAdapter da = new OleDbDataAdapter("Select * From [" + sheetName + "$]", conn);
                DataTable dtExcel = new DataTable();
                da.Fill(dtExcel);
                da.InsertCommand = eh.GetInsertCommand(sheetName, dt, conn);
                dtExcel.Rows.Add("\r\n");       //空2行，顺丰就是从第3行开始的。
                dtExcel.Rows.Add("\r\n");
                DataRow dr_excel;
                string ColumnName;
                foreach (DataRow dr in dt.Select(filter))
                {
                    dr_excel = dtExcel.NewRow();
                    foreach (DataColumn c in dt.Columns)
                    {
                        ColumnName = c.ColumnName;
                        dr_excel[ColumnName] = dr[ColumnName];
                    }
                    dtExcel.Rows.Add(dr_excel);
                }
                da.Update(dtExcel);
                conn.Close();
                return "导出到顺丰成功";

            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                return ex.Message;
            }
        }

        /// <summary>
        /// 获取单元格的值
        /// <para>2013-7-2 by sutroon</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="columnName"></param>
        /// <param name="defv"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        public static object GetValue(this DataTable dt, string columnName, object defv = null, int rowIndex = 0)
        {
            if (dt.Rows.Count == 0) return defv;
            object v = dt.Rows[rowIndex][columnName];
            return v == DBNull.Value ? defv : v;
        }
        public static string GetString(this DataTable dt, string columnName, string defv = "", int rowIndex = 0, bool emptyAsNull = false)
        {
            if (dt == null || dt.Rows.Count == 0 || !dt.Columns.Contains(columnName)) return defv;
            object v = dt.Rows[rowIndex][columnName];
            return v == DBNull.Value || (emptyAsNull && string.IsNullOrEmpty(v.ToString())) ? defv : v.ToString();
        }
        public static string GetStringByFilter(this DataTable dt, string columnName, string filter, string defv = "")
        {
            if (dt == null || dt.Rows.Count == 0) return defv;
            DataRow r = dt.Select(filter).FirstOrDefault();
            if (r == null) return defv;
            return r[columnName] == DBNull.Value ? defv : r[columnName].ToString();
        }
        public static int GetInt(this DataTable dt, string columnName, int defv = 0, int rowIndex = 0)
        {
            if (dt.Rows.Count == 0) return defv;
            object v = dt.Rows[rowIndex][columnName];
            return v == DBNull.Value ? defv : (int)v;
        }
        public static double GetDouble(this DataTable dt, string columnName, double defv = 0, int rowIndex = 0)
        {
            if (dt.Rows.Count == 0) return defv;
            object v = dt.Rows[rowIndex][columnName];
            return v == DBNull.Value ? defv : (double)v;
        }
        public static DateTime GetDateTime(this DataTable dt, string columnName, DateTime defv, int rowIndex = 0)
        {
            if (dt.Rows.Count == 0) return defv;
            object v = dt.Rows[rowIndex][columnName];
            return v == DBNull.Value ? defv : (DateTime)v;
        }
        public static Guid GetGuid(this DataTable dt, string columnName, Guid defv, int rowIndex = 0)
        {
            if (dt.Rows.Count == 0) return defv;
            object v = dt.Rows[rowIndex][columnName];
            return v == DBNull.Value ? defv : (Guid)v;
        }

        /// <summary>
        /// DataTable转换成Json数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        /// <remarks>2014-3-8 by sutroon</remarks>
        public static string ToJsonString(this DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0) return string.Empty;

            StringBuilder jsonBuilder = new StringBuilder();
            jsonBuilder.Append("[");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                jsonBuilder.Append("{");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    jsonBuilder.Append("\"");
                    jsonBuilder.Append(dt.Columns[j].ColumnName);
                    jsonBuilder.Append("\":\"");
                    jsonBuilder.Append(dt.Rows[i][j].ToString());
                    jsonBuilder.Append("\",");
                }
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
                jsonBuilder.Append("},");
            }
            jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("]");
            return jsonBuilder.ToString();
        }

        public static string GetString(this DataRowView drv, string columnName, string defv = "", int rowIndex = 0, bool emptyAsNull = false)
        {
            if (drv == null || !drv.Row.Table.Columns.Contains(columnName)) return defv;
            object v = drv[columnName];
            return v == DBNull.Value || (emptyAsNull && string.IsNullOrEmpty(v.ToString())) ? defv : v.ToString();
        }

        public static string GetString(this DataRow dr, string columnName, string defv = "", int rowIndex = 0, bool emptyAsNull = false)
        {
            if (dr == null || !dr.Table.Columns.Contains(columnName)) return defv;
            object v = dr[columnName];
            return v == DBNull.Value || (emptyAsNull && string.IsNullOrEmpty(v.ToString())) ? defv : v.ToString();
        }
    }
}
